using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for BookingDAO
/// </summary>
public class BookingDAO:DBAccess
{

    #region sql Commands   
    string SQL_SELECT_ALL = "select * from Booking";
    string SQL_SELECT_BY_ID = "select * from Booking where ID=@id";
    string SQL_INSERT = "insert into Booking(CustomerID,ModifiedDate,ModifiedBy,Type,State) values(@CustomerID, @ModifiedDate,@ModifiedBy,@Type,@State)";
    string SQL_UPDATE = "update Booking set CustomerID = @CustomerID,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy, Type=@type,State=@State where ID = @id";
    string SQL_UPDATE_STATUS = "update Booking set State=@state where ID=@id";
    string SQL_DELETE = "delete from Booking where ID = @id";

    //statistic
    string SQL_SELECT_TODAY_BOOKING = "select count(*) from Booking where datepart(day,ModifiedDate)=datepart(day,@date)"
+" and datepart(month,ModifiedDate)=datepart(month,@date) "
+" and datepart(year,ModifiedDate)=datepart(year,@date)";
    string SQL_SELECT_MONTH_BOOKING = "select count(*) from Booking where datepart(year, ModifiedDate)=datepart(year,@date) and datepart(month, ModifiedDate)=datepart(month,@date)";

    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public BookingDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methods
    /// <summary>
    /// Get booking by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Booking GetBooking(int id)
    {
        Booking boo = new Booking();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    boo.BookingID = dr.GetInt32(0);
                    boo.Type = dr.GetByte(1);
                    boo.CustomerID = dr.GetInt32(2);                   
                    boo.ModifiedDate = dr.GetDateTime(3);
                    boo.ModifiedBy = dr.GetInt32(4);
                    boo.State = dr.GetByte(5);
                   
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return boo;
    }
    /// <summary>
    /// Get all Booking 
    /// </summary>
    /// <returns></returns>
    public IList<Booking> GetAllBookings()
    {
        IList<Booking> list = new List<Booking>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Booking boo = new Booking();
                    boo.BookingID = dr.GetInt32(0);
                    boo.CustomerID = dr.GetInt32(1);
                    boo.ModifiedDate = dr.GetDateTime(2);
                    boo.ModifiedBy = dr.GetInt32(3);
                    list.Add(boo);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
   /// <summary>
    /// Insert new Booking into database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Insert(Booking boo)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_INSERT, conn);
           command.Parameters.AddWithValue("@CustomerID", boo.CustomerID);         
           command.Parameters.AddWithValue("@ModifiedDate", boo.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", boo.ModifiedBy);
           command.Parameters.AddWithValue("@Type",boo.Type);
           command.Parameters.AddWithValue("@State", boo.State);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               command.CommandText = "select @@IDENTITY from Booking";
               boo.BookingID = int.Parse(command.ExecuteScalar().ToString());
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Update Booking
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Update(Booking boo)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
           command.Parameters.AddWithValue("@CustomerID", boo.CustomerID);
           command.Parameters.AddWithValue("@ModifiedDate", boo.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", boo.ModifiedBy);
           command.Parameters.AddWithValue("@Type", boo.Type);
           command.Parameters.AddWithValue("@State", boo.State);
           command.Parameters.AddWithValue("@id", boo.BookingID);           
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

    /// <summary>
   /// Update state of a booking
   /// 
    /// </summary>
    /// <param name="bId">Bookinging ID</param>
    /// <param name="state">new state to update</param>
    /// <returns></returns>
    public int UpdateState(int bId, byte state)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE_STATUS, conn);      
            command.Parameters.AddWithValue("@State", state);
            command.Parameters.AddWithValue("@id", bId);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
   /// <summary>
   /// Remove Booking out of database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Delete(int Id)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_DELETE, conn);
           command.Parameters.AddWithValue("@id", Id);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

    public DataTable GetBookings(int currentPage, int pageSize, ref int total, string keyword)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        try
        {
            SqlCommand cmd = new SqlCommand("sp_SelectBookingsExt");
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@pagesize", SqlDbType.Int);
            cmd.Parameters.Add("@currentpage", SqlDbType.Int);
            cmd.Parameters.Add("@customerName", SqlDbType.NVarChar);

            cmd.Parameters.Add("@totals", SqlDbType.Int);
            cmd.Parameters["@pagesize"].Value = pageSize;
            cmd.Parameters["@currentpage"].Value = currentPage;
            cmd.Parameters["@customerName"].Value = keyword;

            cmd.Parameters["@totals"].Direction = ParameterDirection.Output;
            cmd.Parameters["@totals"].Value = total;
            sda.SelectCommand = cmd;
            if (Connect())
            {
                sda.Fill(ds, "booking_table");
                total = int.Parse(cmd.Parameters["@totals"].Value.ToString());
                Disconnect();
            }
        }
        catch (Exception ex) { }
        return ds.Tables["booking_table"];
    }

    public int GetBookingCountByDate(DateTime date)
    {
        int quantity = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_TODAY_BOOKING, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@date", date);
                quantity = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return quantity;
    }

    public int GetBookingCountByMonth(DateTime date)
    {
        int quantity = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_MONTH_BOOKING, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@date", date);
                quantity = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return quantity;
        
    }
    #endregion
}
